﻿using System;
using System.Collections.Generic;
using System.Text;
using NLBS.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace NLBS.Data
{
    public partial class SqlDataProvider : DbProvider
    {
        #region guestBook

        public override IList<GuestBook> GetGuestBook()
        {

            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT gb_id, gb_username, gb_userID, gb_content, gb_editMark, gb_ubbFlags, gb_postTime, gb_replyUsername, gb_reply, gb_replyTime, gb_hidden, gb_ip FROM dbo.Guestbook
            ORDER BY gb_postTime DESC");
            IList<GuestBook> lists = new List<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
            }
            return lists;
        }

        public override IPagedCollection<GuestBook> GetGuestBookBySearch(int startIndex, int pageSize, string prefix)
        {
            StringBuilder sb = new StringBuilder();
            string[] prefixArray = prefix.Split(new char[] { ' ' });

            for (int i = 0; i < prefixArray.Length; i++)
            {
                if (i > 0)
                {
                    sb.Append("AND ");
                }
                sb.AppendFormat("gb_content LIKE '%{0}%'", prefixArray[i]);
            }
            //comm_content
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT top {0} gb.*, 
            FROM [Guestbook] gb 
            WHERE gb_id<=(select min(gb_id) 
            from (select top {1} gb_id from Guestbook order by gb_id desc) as T) AND {2} 
            ORDER BY gb_postTime DESC;
            SELECT COUNT(gb_id) AS TotalRecords from Guestbook where {2}", startIndex, pageSize, sb.ToString()));
            IDataReader dr = db.ExecuteReader(dbCommand);
            IPagedCollection<GuestBook> lists = new PagedCollection<GuestBook>();
            GuestBook gb = null;
            while (dr.Read())
            {
                gb = CommonDataProvider.PopulateGuestBookFromIDataReader(dr);
                //comment.Article = PopulateCommentArticleFromIDataReader(dr);
                lists.Add(gb);
            }
            lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dr);
            return lists;
        }

        public override IList<GuestBook> GetTopGuestBook()
        {
            DbCommand dbCommand = db.GetStoredProcCommand("GetTopGuestBook");
            IList<GuestBook> lists = new List<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
            }
            return lists;
        }

        public override GuestBook GetGuestBookByID(int gbookID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT  *
            FROM      Guestbook
            WHERE   (gb_id = @gbookID)");
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbookID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            GuestBook guestBook = null;
            if (dr.Read())
            {
                guestBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dr);
            }
            return guestBook;
        }

        public override int InsertGuestBook(GuestBook gbook)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO [dbo].[Guestbook] ([gb_username], [gb_userID], 
            [gb_content], [gb_editMark], [gb_ubbFlags], [gb_postTime], [gb_replyUsername], 
            [gb_reply], [gb_replyTime], [gb_hidden], [gb_ip]) VALUES 
            (@username, @userID, @content, @editMark, @ubbFlags, @postTime, 
            @replyUsername, @reply, @replyTime, @hidden, @ip)");
            db.AddInParameter(dbCommand, "userName", DbType.String, gbook.UserName);
            db.AddInParameter(dbCommand, "userID", DbType.Int32, gbook.UserID);
            db.AddInParameter(dbCommand, "content", DbType.String, gbook.Content);
            db.AddInParameter(dbCommand, "editMark", DbType.String, gbook.EditMark);
            db.AddInParameter(dbCommand, "ubbFlags", DbType.String, gbook.UbbFlags);
            db.AddInParameter(dbCommand, "postTime", DbType.DateTime, gbook.PostTime);
            db.AddInParameter(dbCommand, "replyUserName", DbType.String, gbook.ReplyUserName);
            db.AddInParameter(dbCommand, "reply", DbType.String, gbook.Reply);
            db.AddInParameter(dbCommand, "replyTime", DbType.DateTime, gbook.ReplyTime);
            db.AddInParameter(dbCommand, "hidden", DbType.Boolean, gbook.Hidden);
            db.AddInParameter(dbCommand, "ip", DbType.String, gbook.IP);
           return db.ExecuteNonQuery(dbCommand);
        }

        public override void UpdateGuestBook(GuestBook gbook)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"UPDATE  Guestbook
            SET         gb_replyUsername = @replyUsername, gb_reply = @reply, gb_replyTime = @replyTime
            WHERE   (gb_id = @gbookID)");
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbook.GuestBookID);
            db.AddInParameter(dbCommand, "replyUserName", DbType.String, gbook.ReplyUserName);
            db.AddInParameter(dbCommand, "reply", DbType.String, gbook.Reply);
            db.AddInParameter(dbCommand, "replyTime", DbType.DateTime, gbook.ReplyTime);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteGuestBook(int gbookID)
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("DELETE FROM Guestbook WHERE (gb_id = @gbookID)");
            db.AddInParameter(dbCommand, "gbookID", DbType.Int32, gbookID);
            db.ExecuteNonQuery(dbCommand);
        }

        public override int GetAllGuestBookCount()
        {
            
            DbCommand dbCommand = db.GetSqlStringCommand("SELECT COUNT(gb_id) FROM GuestBook");
            return Convert.ToInt32(db.ExecuteScalar(dbCommand));
        }

        public override IPagedCollection<GuestBook> GetAllGuestBookByPage(int startIndex, int pageSize)
        {
            string strPageSQL = string.Format(@"select top {0} * FROM Guestbook art
            left join blog_category cat on art.log_catid=cat.cat_id where log_id<=(select min(gb_id) 
            from (select top {1} gb_id from guesbook order by gb_id desc) as T)
            order by gb_postTime desc;
            SELECT COUNT(gb_id) AS TotalRecords from guesbook", pageSize, startIndex);

            DbCommand dbCommand = db.GetSqlStringCommand(strPageSQL);

            IPagedCollection<GuestBook> lists = new PagedCollection<GuestBook>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    GuestBook gutstBook = CommonDataProvider.PopulateGuestBookFromIDataReader(dataReader);
                    lists.Add(gutstBook);
                }
                lists.MaxItems = CommonDataProvider.PopulateTotalRecordsFromIDataReader(dataReader);
            }
            return lists;
        }

        #endregion
    }
}
